package com.yellowpage.bigdata.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;

import com.yellowpage.bigdata.data.NspOnofflogImeiNewAddDay;
import com.yellowpage.bigdata.dao.NspOnofflogImeiNewAddDayDao;

public class NspOnofflogImeiNewAddDayServiceImpl extends BaseServiceImpl implements NspOnofflogImeiNewAddDayService {
	  private NspOnofflogImeiNewAddDayDao nspOnofflogImeiNewAddDayDao;
	
	  public void setNspOnofflogImeiNewAddDayDao(NspOnofflogImeiNewAddDayDao nspOnofflogImeiNewAddDayDao) {
		  this.nspOnofflogImeiNewAddDayDao = nspOnofflogImeiNewAddDayDao;
	  }
	
	
	  /* Smooth generate Method */ 
	  public NspOnofflogImeiNewAddDay getNspOnofflogImeiNewAddDay(Long id){
	      return nspOnofflogImeiNewAddDayDao.get(id);
	  }
	  
    public List<NspOnofflogImeiNewAddDay> getAllNspOnofflogImeiNewAddDay(){
        return nspOnofflogImeiNewAddDayDao.getAll();
    }
    
    public List<Map> queryNspOnofflogImeiNewAddDayByPage(Map queryMap, PageRender pageRender) {	
    	String t2 = queryMap.get("regionCode").equals("") ? queryMap.get("regionCode1").toString() : " and REGION_CODE in "+queryMap.get("regionCode");
        String t3 ="";
        String value="";
    	if(queryMap.get("channelNo").equals("") && queryMap.get("channelNo1") !=null){
    		t3 = " and CHANNEL_NO in " + queryMap.get("channelNo1");
    		value= " and VALUE in " + queryMap.get("channelNo1");
        	 //t3 = queryMap.get("channelNo").equals("") && queryMap.get("channelNo1") !=null ? " and CHANNEL_NO in " + queryMap.get("channelNo1").toString() : " and CHANNEL_NO = "+queryMap.get("channelNo");
        }else if(queryMap.get("channelNo").equals("") && queryMap.get("channelNo1") ==null){
        	t3 = "";
        	value="";
        }else{
        	t3= " and CHANNEL_NO = '"+queryMap.get("channelNo") +"'";
        	value= " and VALUE = '"+queryMap.get("channelNo") +"'";
        }
    	String startTime =queryMap.get("startTime").equals("") ? "" : " and LOG_DATE  >=  "+queryMap.get("startTime");
    	String endTime =queryMap.get("endTime").equals("") ? "" : " and LOG_DATE  <=  "+queryMap.get("endTime");
    	//省份渠道下没有的显示0.
    	QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL("SELECT * FROM (SELECT t3.LOG_DATE,t3.REGION_CODE,t3.CHANNEL_NO,SUM(IMEI_DAY_COUNT) IMEI_DAY_COUNT,SUM(IMEI_ALL_COUNT) IMEI_ALL_COUNT,SUM(IMEI_NEW_COUNT) IMEI_NEW_COUNT,SUM(IMSI) IMSI,SUM(IMEI_NEW_ALL_COUNT) IMEI_NEW_ALL_COUNT,"
    			+ "(SELECT b.NAME FROM OMC_REGION b WHERE b.REGION_CODE = t3.REGION_CODE) as REGION_NAME,"
    			+ "(SELECT c.NAME FROM OMC_DIC_TABLE c WHERE c.VALUE = t3.CHANNEL_NO AND c.DIC_NAME = 'NSP_ONOFFLOG_CHANNEL_NO') as CHANNEL_NAME"
    			+ " FROM ("
    			+ "SELECT  t1.LOG_DATE , t1.REGION_CODE  , t1.CHANNEL_NO , t1.IMEI_DAY_COUNT , t1.IMEI_ALL_COUNT , t1.IMEI_NEW_COUNT ,t1.IMSI , t1.IMEI_NEW_ALL_COUNT FROM NSP_ONOFFLOG_IMEI_NEW_ADD_DAY t1 WHERE 1=1 " + t2 + t3 + startTime + endTime
    			+ " UNION all "
    			+ "SELECT t22.LOG_DATE,t21.REGION_CODE,t21.VALUE CHANNEL_NO ,0 IMEI_DAY_COUNT,0 IMEI_ALL_COUNT,0 IMEI_NEW_COUNT,0 IMSI,0 IMEI_NEW_ALL_COUNT FROM"
    			+ "(SELECT REGION_CODE,VALUE from OMC_DIC_REGION WHERE 1=1 " + t2 + value 
    			+ ") t21,"
    			+ "(SELECT DISTINCT LOG_DATE from NSP_ONOFFLOG_IMEI_NEW_ADD_DAY WHERE 1=1 " + startTime + endTime
    			+ " ) t22) t3 group by t3.LOG_DATE,t3.REGION_CODE,t3.CHANNEL_NO) t33"
    			+ "  WHERE t33.CHANNEL_NAME IS NOT NULL AND t33.IMEI_ALL_COUNT>10 AND t33.CHANNEL_NAME LIKE '%"+queryMap.get("channelName")+"%'");
    	
    	

							    ;
		List<Map> resultList;

		//当pageRender存在的时候，跳转
		if (pageRender != null)
		{ resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);}

		//否则取全量
		else

		{ resultList = sqlAssistant.query(
				baseDao.getConnection(), queryMap);}

		return resultList;
	}
    
    
    public void saveNspOnofflogImeiNewAddDay(NspOnofflogImeiNewAddDay nspOnofflogImeiNewAddDay){
        nspOnofflogImeiNewAddDayDao.save(nspOnofflogImeiNewAddDay);
    }
    
	  public void updateNspOnofflogImeiNewAddDay(NspOnofflogImeiNewAddDay nspOnofflogImeiNewAddDay){
	      nspOnofflogImeiNewAddDayDao.update(nspOnofflogImeiNewAddDay);
	  }
	  
	  public void delete(List deleteList){
	      nspOnofflogImeiNewAddDayDao.remove(deleteList);
	  }


	@Override
	public String queryChannol(String regionCode) {
		// TODO Auto-generated method stub
		
		String sql = "SELECT value FROM OMC_DIC_REGION where 1=1 " + regionCode;
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(sql);
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), null);
		String str = "(";
		if(resultList != null && resultList.size() >0){
			for (Map map : resultList) {
				str += "'" + (String) map.get("VALUE") + "',";
			}
			str= str.substring(0, str.length() - 1)+")";
			return str;
		}
		return "";
	}
	  
	  
	 
	
}	